package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.rmt.data.PaymentTypeDataList;
import com.mbc.rmt.entities.AddCode;
import com.mbc.rmt.entities.PaymentType;
import com.mbc.rmt.entities.PaymentTypeDetail;
import com.mbc.rmt.entities.PaymentTypeRpt;
import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaParams;

public class PaymentTypeDAOImpls extends DAO<PaymentType> implements PaymentTypeDAO {

	public PaymentTypeDAOImpls(Connection conn)
	{
		this.conn = conn;
		this.table_name = "PaymentType";
	}

	@Override
	public void create(PaymentType entity) throws SQLException {
		query = "insert into " + table_name + " ( PaymentTypeId, Code, Description, branchID) values( ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setLong(i++, entity.getPaymentTypeId());
		pstmt.setString(i++, entity.getCode());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getBranchID());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(PaymentType entity) throws SQLException {
		query = "update " + table_name + " set  Code = ?, Description = ?, branchID = ?" 
				+ " where PaymentTypeId = " + entity.getPaymentTypeId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getCode());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getBranchID());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where PaymentTypeId = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public PaymentType find(long key) throws SQLException {
		PaymentType l_PaymentType = null;
		query = getSelectQuery() + " where PaymentTypeId = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_PaymentType = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_PaymentType;
	}

	@Override
	public ArrayList<PaymentType> findAll() throws SQLException {
		ArrayList<PaymentType> l_PaymentTypeList = new ArrayList<PaymentType>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_PaymentTypeList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_PaymentTypeList;
	}

	@Override
	protected PaymentType readRecord(ResultSet rs) throws SQLException {
		PaymentType l_entity = new PaymentType();
		l_entity.setPaymentTypeId(rs.getLong("PaymentTypeId"));
		l_entity.setCode(rs.getString("Code"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setBranchID(rs.getLong("branchID"));
		return l_entity;
	}

	@Override
	public PaymentTypeDataList findPaymentTypes(CriteriaParams cri)
			throws SQLException {
		PaymentTypeDataList l_DataList = new PaymentTypeDataList();
		ArrayList<PaymentType> l_entityList = new ArrayList<PaymentType>();
		String l_cri = " and Code like N'%" + cri.getKeyword() + "%'" +
				" or Description like N'%" + cri.getKeyword() + "%'" ;
				
		query = "set rowcount " + cri.getSize() +
			" select * from (select row_number() over(order by PaymentTypeId) as [rowno], * from " + table_name +
			" where 1 = 1" + l_cri + ") " + table_name +
			" where rowno > " + cri.getIndex()+ " and BranchId = " + cri.getBranchId();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()) {
			do {	
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(PaymentTypeId) from " + table_name + " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}
	//MBC Begin
	// For Report
		public ResultSet preparePaymentTypeReport(PaymentTypeRpt info) throws SQLException {
			ResultSet l_rs;
			String l_query = "dbo.sp_cr_paymenttyperpt ?, ?, ?, ?, ?, ?";

			CallableStatement l_pstmt = conn.prepareCall(l_query);
			PaymentTypeRptParameter(l_pstmt, info);
			l_rs = l_pstmt.executeQuery();
			return l_rs;

		}

		private void PaymentTypeRptParameter(CallableStatement l_pstmt,
				PaymentTypeRpt info) throws SQLException {
			int i = 1;

			l_pstmt.setString(i++, info.getFromPaymentTypeID());
			l_pstmt.setString(i++, info.getToPaymentTypeID());
			l_pstmt.setString(i++, info.getFromAddcodeID());
			l_pstmt.setString(i++, info.getToAddcodeID());
			l_pstmt.setString(i++, info.getOrderby());
			l_pstmt.setLong(i++, info.getBranchID());
			
		}
		//MBC End

		@Override
		public PaymentType getPaymentType(String code) throws SQLException {
			PaymentType l_PaymentType = new PaymentType();
			query = "select pt.PaymentTypeId, pt.Code PaymentTypeCode,pt.Description, " +
					"ac.Code AddCode,ac.Sender " +
					"from PaymentType pt " +
					"left join PaymentTypeDetail ptd on ptd.PaymentTypeId = pt.PaymentTypeId " +
					"left join AddCode ac on ac.AddCodeId = ptd.AddCodeId " +
          					"where pt.Code = '" + code + "'";
			pstmt = conn.prepareStatement(query);
			rs = pstmt.executeQuery();
			if (rs.next())
				l_PaymentType = readRecord1(rs);
			rs.close();
			pstmt.close();
			return l_PaymentType;
		}
		protected PaymentType readRecord1(ResultSet rs) throws SQLException {
			PaymentType l_entity = new PaymentType();
			AddCode l_info = new AddCode();
			l_entity.setPaymentTypeId(rs.getLong("PaymentTypeId"));
			l_entity.setCode(rs.getString("PaymentTypeCode"));
			l_entity.setDescription(rs.getString("Description"));
			l_info.setCode(rs.getString("AddCode"));
			l_info.setSender(rs.getString("Sender"));

			return l_entity;
		}

		@Override
		public ArrayList<PaymentTypeDetail> findPaymentTypeDetails(
				long paymentTypeID) throws SQLException {
			ArrayList<PaymentTypeDetail> l_list = new ArrayList<PaymentTypeDetail>();

			query = "select PaymentTypeDetailId,ptd.PaymentTypeId,ac.AddCodeId,ac.Code,ac.Sender " + 
					"from PaymentTypeDetail ptd " + 
					"inner join AddCode ac on ac.AddCodeId = ptd.AddCodeId " +
					"where ptd.PaymentTypeId = "+ paymentTypeID ; 
//					"order by bcd.srno";

			pstmt = conn.prepareStatement(query);
			rs = pstmt.executeQuery();
			if(rs.next()){
				do{
					l_list.add(readRecords(rs));
				}while (rs.next());
			}
			rs.close();
			pstmt.close();
			return l_list;
		}
		protected PaymentTypeDetail readRecords(ResultSet rs) throws SQLException {
			PaymentTypeDetail l_entity = new PaymentTypeDetail();

			l_entity.setPaymentTypeDetailId(rs.getLong("PaymentTypeDetailId"));
			l_entity.setPaymentTypeId(rs.getLong("PaymentTypeId"));
			l_entity.setAddCodeId(rs.getLong("AddCodeId"));
			l_entity.setAddCode(rs.getString("Code"));
			l_entity.setSender(rs.getString("Sender"));
			
			return l_entity;
		}

		@Override
		public PaymentType findPaymentTypeByCode(String code) throws SQLException {
			PaymentType l_PaymentType = null;
			query = getSelectQuery() + " where Code = '" + code + "'";
			pstmt = conn.prepareStatement(query);
			rs = pstmt.executeQuery();
			if (rs.next())
				l_PaymentType = readRecord(rs);
			rs.close();
			pstmt.close();
			return l_PaymentType;
		}

}
